package edu.hawaii.myisern.model;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.DatabaseMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * Provides SQL database interface to the application.
 * 
 * Note: In embedded mode, connections are "free", so we make no effort to manage them via pooling.
 * 
 * @author Philip Johnson
 * @author Randy Cox
 */
public class Database {

  private final String className = "Database";

  /** The JDBC driver. */
  // private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
  private String driver = "org.apache.derby.jdbc.ClientDriver";

  /** The Derby connection URL. */
  // private String connectionURL = "jdbc:derby:myisern;create=true";
  private String connectionURL = "jdbc:derby://localhost:1527/myisern;create=true";

  /** Command to shutdown system. */
  // private String shutdownURL = "jdbc:derby:;shutdown=true";
  /** The key for putting/retrieving the directory where Derby will create its databases. */
  private static final String derbySystemKey = "derby.system.home";

  /** Connection to database * */
  private Connection connection = null;

  /** Short cut for start of SQL statement. */
  public final String CREATE_TABLE = "CREATE TABLE ";

  /** Short cut for start of SQL statement. */
  public final String DROP_TABLE = "DROP TABLE ";

  /** Short cut for start of SQL statement. */
  public final String SELECT_FROM = "SELECT * FROM ";

  /** Short cut for start of SQL statement. */
  public final String INSERT_INTO = "INSERT INTO ";

  /** Short cut for start of SQL statement. */
  public final String UPDATE = "UPDATE ";

  /** Short cut for start of SQL statement. */
  public final String DELETE_FROM = "DELETE FROM ";

  /** Short cut for start of SQL statement. */
  public final String SELECT_COUNT = "SELECT COUNT(*) FROM ";

  /**
   * Creates the persistent store for the myisern in ~/.myisern/db.
   * 
   * @param args for passing in custom driver.
   */
  public Database(String... args) {
    String methodName = ".Database ";

    // Set custom driver is passed into constructor. For testing failures.
    if (args.length > 0) {
      driver = args[0];
    }

    // Make sure the directory exists, creating it if not present.
    File myIsernHome = new File(System.getProperty("user.home"), ".myisern");
    myIsernHome.mkdirs();
    File dbHome = new File(myIsernHome, "db");
    dbHome.mkdirs();

    // Set the system property used by Derby to locate the db files.
    Log.enter(className + methodName + "Database location " + dbHome.getAbsolutePath());
    System.getProperties().put(derbySystemKey, dbHome.getAbsolutePath());
  }

  /**
   * Connect to database, create database if it does not exist.
   */
  public void connect() {
    String methodName = ".connect() ";
    try {
      Class.forName(driver);
      Log.enter(className + methodName + "Database driver loaded: " + driver);
      connection = DriverManager.getConnection(connectionURL);
      Log.enter("Database connected: " + connectionURL);
    }
    catch (Throwable e) {
      String msg = className + methodName + "Exception ";
      msg = msg.concat(" " + e.getMessage());
      Log.error(msg);
      throw new RuntimeException(msg, e);
    }
  }

  /**
   * Shutdown db, it will always throws a SQLException.
   */
  // public void disconnect() {
  // String methodName = ".disconnect() ";
  // try {
  // this.connection.close();
  // String msg = className + methodName + "completed.";
  // Log.enter(msg);
  // }
  // catch (Exception e) { //NOPMD
  // // Not needed.
  // }
  // }
  /**
   * Shutdown db, it will always throws a SQLException.
   */
  // public void shutdown() {
  // String methodName = ".disconnect() ";
  // try {
  // this.connection.close();
  // DriverManager.getConnection(this.shutdownURL);
  // }
  // catch (Exception e) {
  // String msg = className + methodName + "completed.";
  // Log.enter(msg);
  // }
  // }
  /**
   * Shows tables currently in database.
   * 
   * @return List of tables in database.
   */
  public List<String> getTables() {
    String methodName = ".getTables() ";
    List<String> list = new ArrayList<String>();
    List<List<String>> table;
    ResultSet results = null;
    try {
      DatabaseMetaData metaData = this.connection.getMetaData();
      results = metaData.getTables(null, null, null, null);
      table = buildTable(results);
    }
    catch (Exception e) {
      String msg = className + methodName + "Exception ";
      msg = msg.concat(" " + e.getMessage());
      Log.error(msg);
      throw new RuntimeException(msg, e);
    }
    finally {
      try {
        if (results != null) {
          results.close();
        }
      }
      catch (Exception e) { // NOPMD
        // Out of options.
      }
    }

    // Get table name from table, column 2.
    for (List<String> row : table) {
      list.add(row.get(2));
    }
    return list;
  }

  /**
   * Check if table is available in database.
   * 
   * @param tableName name of table to check for.
   * @return true if table is found.
   */
  public Boolean hasTable(String tableName) {
    String methodName = ".hasTable() ";
    List<String> tables = getTables();
    Boolean found = false;
    for (String table : tables) {
      if (table.contentEquals(tableName)) {
        found = true;
      }
    }
    Log.enter(className + methodName + tableName + " found = " + found.toString());
    return found;
  }

  /**
   * Generic SQL execute command, that does not return results.
   * 
   * @param sql statement for command.
   */
  public void execute(String sql) {
    String methodName = ".execute() ";
    Log.enter(className + methodName + "start");
    Statement statement = null;
    try {
      statement = this.connection.createStatement();
      Boolean result = statement.execute(sql);
      Log
          .enter(className + methodName + "[" + sql + "] result set returned = "
              + result.toString());
    }
    catch (Throwable e) {
      String msg = className + methodName + "Exception [" + sql;
      msg = msg.concat("] " + e.getMessage());
      Log.error(msg);
      throw new RuntimeException(msg, e);
    }
    finally {
      try {
        statement.close();
        this.connection.commit();
      }
      catch (Exception e) { // NOPMD
        // Out of options.
      }
    }
    Log.enter(className + methodName + "end");
  }

  /**
   * Updates table.
   * 
   * @param sql statement for update.
   * @param args list of arguments to insert in sql statement.
   * @return count of rows updated.
   */
  public Integer update(String sql, String... args) {
    String methodName = ".update() ";
    Log.enter(className + methodName + "start");
    PreparedStatement statement = null;
    Integer result = 0;
    try {
      statement = this.connection.prepareStatement(sql);
      Log.enter("sql = " + sql);
      for (int i = 0; i < args.length; i++) {
        Log.enter("args = " + args[i]);
        String item = "";
        if ((args[i] != null) && (!args[i].isEmpty())) {
          item = args[i].substring(0, Math.min(254, args[i].length()));
        }
        item = item.trim();
        if (item.isEmpty()) {
          item = " ";
        }
        Log.enter("param = " + item);
        statement.setString(i + 1, item);
      }
      result = statement.executeUpdate();
      Log.enter("result set returned? = " + result.toString());
    }
    catch (Throwable e) {
      String msg = className + methodName + "Exception [" + sql;
      msg = msg.concat("] " + e.getMessage());
      Log.error(msg);
      throw new RuntimeException(msg, e);
    }
    finally {
      try {
        statement.close();
        this.connection.commit();
      }
      catch (Exception e) { // NOPMD
        // Out of options.
      }
    }
    Log.enter(className + methodName + "end");
    return result;
  }

  /**
   * Query table and return results as a 2-D List<String>.
   * 
   * @param sql statement for query.
   * @param args list of paramaters to insert into sql string.
   * @return 2-D List representing table results.
   */
  public List<List<String>> query(String sql, String... args) {
    String methodName = ".query() ";
    PreparedStatement statement = null;
    ResultSet results = null;
    List<List<String>> table = new ArrayList<List<String>>();
    try {
      statement = this.connection.prepareStatement(sql);
      for (int i = 0; i < args.length; i++) {
        String item = "";
        if ((args[i] != null) && (!args[i].isEmpty())) {
          item = args[i].substring(0, Math.min(254, args[i].length()));
        }
        statement.setString(i + 1, item);
      }
      results = statement.executeQuery();
      table = buildTable(results);
      Log.enter(className + methodName + "[" + sql + "] result count = " + table.size());
    }
    catch (Throwable e) {
      String msg = className + methodName + "Exception [" + sql;
      msg = msg.concat("] " + e.getMessage());
      Log.error(msg);
      throw new RuntimeException(msg, e);
    }
    finally {
      try {
        statement.close();
        this.connection.commit();
        if (results != null) {
          results.close();
        }
      }
      catch (Exception e) { // NOPMD
        // Out of options.
      }
    }
    return table;
  }

  /**
   * Builds a 2-D List of strings from a result set.
   * 
   * @param results from a database query
   * @return 2-D list results
   * @throws Exception if db error occurs.
   */
  private List<List<String>> buildTable(ResultSet results) throws Exception {
    List<List<String>> table = new ArrayList<List<String>>();
    ResultSetMetaData metaData = results.getMetaData();
    Integer columnCount = metaData.getColumnCount();
    while (results.next()) {
      List<String> row = new ArrayList<String>();
      for (int i = 1; i <= columnCount; i++) {
        row.add(results.getString(i));
      }
      table.add(row);
    }
    return table;
  }
}
